package com.lwj.hiring.common.util;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import com.lwj.hiring.common.Column;
import com.lwj.hiring.common.Constants;

/**
 * 数据库操作
 * 
 * @author Lwj
 * @since 2016年9月6日
 */
public class DBUtil {
	private static final Logger LOG = Logger.getLogger(Util.class);
	private static final String DEFAULT_PROPERTIES_NAME = "jdbc";

	private DBUtil() throws IllegalAccessException {
		throw new IllegalAccessException();
	}

	/**
	 * 读取properties内容
	 * 
	 * @param key
	 * @return
	 */
	public static String getDefaultBundle(String key) {
		return getBundle(DEFAULT_PROPERTIES_NAME, key);
	}

	public static String getBundle(String propertiesName, String key) {
		return ResourceBundle.getBundle(propertiesName).getString(key);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection connection = null;
		try {
			Class.forName(Constants.DB_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,
					Constants.DB_USERNAME, Constants.DB_PASSWORD);
		} catch (ClassNotFoundException e) {
			Util.e(LOG, "数据库驱动加载失败！", e);
		} catch (SQLException e) {
			Util.e(LOG, "获取数据库连接失败！", e);

		}
		return connection;
	}

	/**
	 * 增删改操作
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int executeUpdate(String sql, Object... params) {
		int row = 0;
		Connection connection = getConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}
			row = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			Util.e(LOG, "操作数据库失败，", e);
		} finally {
			DBUtil.close(preparedStatement, connection);
		}
		return row;
	}

	public static int executeUpdate(String sql, List<Object> list) {
		return executeUpdate(sql, list.toArray());
	}

	/**
	 * 关闭数据库资源
	 */
	public static void close(PreparedStatement preparedStatement) {
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				Util.e(LOG, "preparedStatement关闭失败！", e);
			}
		}
	}

	public static void close(PreparedStatement preparedStatement,
			Connection connection) {
		close(preparedStatement);
		close(connection);
	}

	public static void close(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				Util.e(LOG, "connection关闭失败！", e);
			}
		}
	}

	public static void close(ResultSet resultSet,
			PreparedStatement preparedStatement, Connection connection) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				Util.e(LOG, "resultSet关闭失败！", e);
			}
		}
		close(preparedStatement, connection);
	}

	/**
	 * 数据库事务
	 * 
	 * @param connection
	 * @param flag
	 */
	public static void setAutoCommit(Connection connection, boolean flag) {
		try {
			connection.setAutoCommit(flag);
		} catch (Exception e) {
			Util.e(LOG, "数据库事物异常！", e);
		}
	}

	/**
	 * 查询，返回对象集合
	 * 
	 * @param clazz
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> List<T> executeQuery(Class<T> clazz, String sql,
			Object... params) {
		return executeQuery(clazz, sql, Arrays.asList(params));
	}

	public static <T> List<T> executeQuery(Class<T> clazz, String sql,
			List<Object> paramList) {
		List<T> resultList = new ArrayList<T>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			setParameter(paramList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			// 读取数据库结构
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			// 得到有多少列
			int columnCount = resultSetMetaData.getColumnCount();

			while (resultSet.next()) {
				// 实例化对象
				T obj = clazz.newInstance();
				// 对每列循环，把对应查询到的值赋给obj里的相应属性
				for (int i = 1; i <= columnCount; i++) {
					// 得到每列的列名
					String columnLabel = resultSetMetaData.getColumnLabel(i);
					// 循环所有属性
					Field[] fields = clazz.getDeclaredFields();
					for (Field field : fields) {
						// 设置可以改变值
						field.setAccessible(true);
						Class<?> fieldClass = field.getType();
						String fieldName = field.getName();
						// 某个属性被@Column注解修饰，表示属性名与数据库列名对应
						if (field.isAnnotationPresent(Column.class)) {
							Column column = field.getAnnotation(Column.class);
							String columnValue = column.value();
							if (columnValue.equals(columnLabel)) {
								setFieldValue(resultSet, obj, columnLabel,
										field, fieldClass);
								break;
							}
						} else {
							if (fieldName.equals(columnLabel)) {
								setFieldValue(resultSet, obj, columnLabel,
										field, fieldClass);
								break;
							}
						}

					}
				}
				resultList.add(obj);
			}
			return resultList;
		} catch (Exception e) {
			Util.e(LOG, "数据查询失败", e);
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return resultList;
	}

	/**
	 * prepareStatement参数设置
	 * 
	 * @param paramList
	 * @param preparedStatement
	 * @throws SQLException
	 */
	private static void setParameter(List<Object> paramList,
			PreparedStatement preparedStatement) throws SQLException {
		if (!paramList.isEmpty()) {
			for (int i = 0; i < paramList.size(); i++) {
				preparedStatement.setObject(i + 1, paramList.get(i));
			}
		}
	}

	/**
	 * 利用反射实现JavaBean的自动赋值
	 * 
	 * @param resultSet
	 * @param obj
	 * @param columnName
	 * @param field
	 * @param fieldClass
	 * @throws IllegalAccessException
	 * @throws SQLException
	 */

	private static <T> void setFieldValue(ResultSet resultSet, T obj,
			String columnName, Field field, Class<?> fieldClass)
			throws IllegalAccessException, SQLException {

		if (fieldClass.equals(int.class) || fieldClass.equals(Integer.class)) {
			field.set(obj, resultSet.getInt(columnName));
		} else if (fieldClass.equals(String.class)) {
			field.set(obj, resultSet.getString(columnName));
		} else if (fieldClass.equals(BigDecimal.class)) {
			field.set(obj, resultSet.getBigDecimal(columnName));
		} else if (fieldClass.equals(Date.class)) {
			field.set(obj, resultSet.getTimestamp(columnName));
		}
	}

	/**
	 * 查询：返回单一对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> T getUniqueResult(Class<T> clazz, String sql,
			List<Object> paramList) {
		List<T> list = executeQuery(clazz, sql, paramList);
		if (list.isEmpty()) {
			return null;
		} else {
			return list.get(0);
		}
	}

	/**
	 * 查询：返回单一对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> T getUniqueResult(Class<T> clazz, String sql,
			Object... params) {
		return getUniqueResult(clazz, sql, Arrays.asList(params));
	}

}
